# -*- coding: utf-8 -*-
##############################################################################
#
#    OpenERP, Open Source Management Solution
#    Copyright (C) 2004-2010 Tiny SPRL (<http://tiny.be>).
#
#    This program is free software: you can redistribute it and/or modify
#    it under the terms of the GNU Affero General Public License as
#    published by the Free Software Foundation, either version 3 of the
#    License, or (at your option) any later version.
#
#    This program is distributed in the hope that it will be useful,
#    but WITHOUT ANY WARRANTY; without even the implied warranty of
#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#    GNU Affero General Public License for more details.
#
#    You should have received a copy of the GNU Affero General Public License
#    along with this program.  If not, see <http://www.gnu.org/licenses/>.
#
##############################################################################

from openerp import tools
from openerp.osv import fields, osv


class card_status_report(osv.osv):
    _name = "card.status.report"
    _description = u"卡券所属状态统计"

    _columns = {
        'level_one_partner_id': fields.many2one('res.partner', u'代理商', readonly=True),
        'level_two_partner_id': fields.many2one('res.partner', u'经销商', readonly=True),
        'product_id': fields.many2one('product.product', u'产品', readonly=True),
        'is_active': fields.boolean(u'是否激活', readonly=True),
        'is_enabled': fields.boolean(u'是否启用', readonly=True),
        'is_exchanged': fields.boolean(u'是否兑换', readonly=True),
        'is_electron': fields.boolean(u'是否电子券', readonly=True),
        'quantity': fields.integer(u'数量'),
        'year': fields.integer(u'年份'),
    }

    #刷新2015卡券状态统计
    def refresh_statistics_2015(self, cr, uid, context=None):
        cr.execute("DELETE FROM card_status_report WHERE year=2015")
        sql = """INSERT INTO card_status_report (level_one_partner_id,
                    level_two_partner_id,
                    product_id,
                    is_active,
                    is_enabled,
                    is_exchanged,
                    is_electron,
                    quantity, year)
            SELECT pcl.level_one_partner_id,
                pcl.level_two_partner_id,
                pcl.product_id,
                pcl.is_active,
                pcl.is_enabled,
                pcl.is_exchanged,
                pcl.is_electron,
                count(*) as quantity,
                2015
            FROM product_card_line pcl WHERE DATE(pcl.create_date)<='2016-05-30'
            GROUP BY pcl.level_one_partner_id,
                pcl.level_two_partner_id,
                pcl.product_id,
                pcl.is_active,
                pcl.is_enabled,
                pcl.is_exchanged,
                pcl.is_electron"""
        cr.execute(sql)

    #刷新2016卡券状态统计
    def refresh_statistics_2016(self, cr, uid, context=None):
        cr.execute("DELETE FROM card_status_report WHERE year=2016")
        sql = """INSERT INTO card_status_report (level_one_partner_id,
                    level_two_partner_id,
                    product_id,
                    is_active,
                    is_enabled,
                    is_exchanged,
                    is_electron,
                    quantity, year)
            SELECT pcl.level_one_partner_id,
                pcl.level_two_partner_id,
                pcl.product_id,
                pcl.is_active,
                pcl.is_enabled,
                pcl.is_exchanged,
                pcl.is_electron,
                count(*) as quantity,
                2016
            FROM product_card_line pcl WHERE DATE(pcl.create_date)>='2016-06-01'
            AND DATE(pcl.create_date)<'2017-06-28'
            GROUP BY pcl.level_one_partner_id,
                pcl.level_two_partner_id,
                pcl.product_id,
                pcl.is_active,
                pcl.is_enabled,
                pcl.is_exchanged,
                pcl.is_electron"""
        cr.execute(sql)

    #刷新2017卡券状态统计
    def refresh_statistics_2017(self, cr, uid, context=None):
        cr.execute("DELETE FROM card_status_report WHERE year=2017")
        sql = """INSERT INTO card_status_report (level_one_partner_id,
                    level_two_partner_id,
                    product_id,
                    is_active,
                    is_enabled,
                    is_exchanged,
                    is_electron,
                    quantity, year)
            SELECT pcl.level_one_partner_id,
                pcl.level_two_partner_id,
                pcl.product_id,
                pcl.is_active,
                pcl.is_enabled,
                pcl.is_exchanged,
                pcl.is_electron,
                count(*) as quantity,
                2017
            FROM product_card_line pcl WHERE DATE(pcl.create_date)>='2017-06-28'
            AND DATE(pcl.create_date)<'2018-05-24'
            GROUP BY pcl.level_one_partner_id,
                pcl.level_two_partner_id,
                pcl.product_id,
                pcl.is_active,
                pcl.is_enabled,
                pcl.is_exchanged,
                pcl.is_electron"""
        cr.execute(sql)

    #刷新2018卡券状态统计
    def refresh_statistics_2018(self, cr, uid, context=None):
        cr.execute("DELETE FROM card_status_report WHERE year=2018")
        sql = """INSERT INTO card_status_report (level_one_partner_id,
                    level_two_partner_id,
                    product_id,
                    is_active,
                    is_enabled,
                    is_exchanged,
                    is_electron,
                    quantity, year)
            SELECT pcl.level_one_partner_id,
                pcl.level_two_partner_id,
                pcl.product_id,
                pcl.is_active,
                pcl.is_enabled,
                pcl.is_exchanged,
                pcl.is_electron,
                count(*) as quantity,
                2018
            FROM product_card_line pcl WHERE DATE(pcl.create_date)>='2018-05-24'
            AND DATE(pcl.create_date)<'2019-06-01'
            GROUP BY pcl.level_one_partner_id,
                pcl.level_two_partner_id,
                pcl.product_id,
                pcl.is_active,
                pcl.is_enabled,
                pcl.is_exchanged,
                pcl.is_electron"""
        cr.execute(sql)